﻿-- =============================================
-- Author:		<赵洪涛>
-- Create date: <2012-02-03>
-- Description:	<大区人资获取订单数据>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Order_GetItem2_20141107] 
	@OId uniqueidentifier,
	@CompanyId varchar(10),
	@rs int
AS
Begin
	Declare @tmpStateName nvarchar(20),@tmpStateId int
	Set @tmpStateName = '未知'

	Select @tmpStateId=StateId From [Order]	Where OId=@OId And CompanyId=@CompanyId
	IF @tmpStateId Is Not Null
		Select @tmpStateName=IsNull(Title,'未知') From [Order_State] Where [Order_State].Id=@tmpStateId

	--成单信息
	Select [Order].Id,IsNull(OrderId,'') As OrderId,CusId
		,IsNull((Select WebAddress From Customer Where Customer.CustomerId=CusId),'') As WebAddress
		,CusName,CusName_BD,CusPerson,CusPersonTel,CusDescription
		,CusEstimate,[Order].TypeId,TypeName,WayId,WayName,ProcessId,ProcessName
		,Case When WayId=6 Then	Salesman + ' (InSide：' + IsNull((Select Top 1 EName From Employee Where UserName In(Select Top 1 InSide From InOutSide Where InOutSide.CusId=[Order].CusId Order By Id Desc)),'') + ')' Else Salesman End As Salesman
		,SalesmanAccount
		,(select CompanyName from Company where Id=@CompanyId) as CompanyName --公司名称		2016.6.2 by lihuiting
		--,(Select DName from Department where Id=(Select DepartmentId From Employee where UserName=SalesmanAccount)) as DepartmentName --部门名称 2016.6.2 by lihuiting
	    --,(Select DName from Department where Id=(Select ParentId From Employee where UserName=SalesmanAccount)) as ParentDepartmentName  --上级部门名称 2016.6.2 by lihuiting
		-- ,(Select DName from Department where Id=(Select ParentId from Department where Id=(Select DepartmentId From Employee where UserName=SalesmanAccount))) as ParentDepartmentName

		 ,Case When (select ParentId from Department where Id=(Select DepartmentId From Employee where UserName=SalesmanAccount))=0 
		            then 
					 (select DName from Department where Id=(Select DepartmentId From Employee where UserName=SalesmanAccount))
		      When (Select ParentId from Department where Id=(select ParentId from Department where Id=(Select DepartmentId From Employee where UserName=SalesmanAccount)))=0 
			     then 
		            (select Dname from Department where Id=(select ParentId from Department where Id=(Select DepartmentId From Employee where UserName=SalesmanAccount)))+'/'+(select DName from Department where Id=(Select DepartmentId From Employee where UserName=SalesmanAccount))
		      Else 

		(select DName from Department where Id=(select ParentId from Department where Id=(select ParentId from Department where Id=(Select DepartmentId From Employee where UserName=SalesmanAccount))))
			+'/'+
			(select Dname from Department where Id=(select ParentId from Department where Id=(Select DepartmentId From Employee where UserName=SalesmanAccount)))
			+'/'+ 
			(select DName from Department where Id=(Select DepartmentId From Employee where UserName=SalesmanAccount))
		   
		   End As ParentDepartmentName
		,TradeDate,[Order].CreateDate
		,IsNull(shjbh,'') As shjbh
		,IsNull(htbh,'') As htbh
		,YingShouPrice
		,IsNull(ShiShouPrice,0) As ShiShouPrice
		,Discount,PaymentTypeId,PaymentType,BankId,Bank,PaymentDescription
		,OtherDescription + '||' + IsNull((Select [Address] From Customer Where Customer.CustomerId=CusId),'') As OtherDescription
		,DepId,[Order].CompanyId,[Order].StateId
		,IsNull(TrackKfName,'') As TrackKfName
		,GenJinKfName
		,IsNull([Order].AuditPerson,'') As AuditPerson
		,IsNull(AuditDate,'1900-01-01') As AuditDate
		,IsNull((Select Title From Order_State Where Id=[Order].StateId),'---') As StateName
		,NOE_Flag,isnull(trackBDName,'') as trackBDName,c.RegTime,c.RegCapital,c.Employees
		,(Select Title from Customer_Employees e where e.id=c.Employees) EmployeesName,c.Introduction,p.Age,p.Weixin
	From [Order] left join Customer c on [Order].CusId=c.CustomerId 
		 left join ContactPerson p on [Order].CusId=p.CustomerId and p.IsMain=1
	Where OId=@OId And [Order].CompanyId=@CompanyId

	--成单项信息
	Select Id,ProductId,ProductName,UnitPrice,Quantity
		,Salesman,SalesmanAccount,DepId,CompanyId
		,CreateDate,Occupancy,IsValid,DESCRIPTION,ProductCateName
	From Order_Item
	Where OId=@OId
	Order by ProductId,Id

	--财务认款信息
	Select Id,GId,CusId,CusName,SalesmanAccount,Salesman
		,CreateDate,Prices,PaymentTypeId,PaymentType
		,BankId,Bank,SWId,SWName,shjbh,FinanceFlag
		,IsNull(FinanceDate,'1900-01-01') As FinanceDate
		,CompanyId,Description
		,(SELECT SUM(Prices) FROM Order_PaymentRecord Where OId=@OId) AS Prices_SUM
	From Order_PaymentRecord
	Where OId=@OId
	Order By FinanceFlag

	If @rs>3
		--项目分析记录
		Select Id,ProjectId,ProductId,CostPrice,SalePrice,Description,
		TypeId,IsFinish,SupplierId,CompanyId,CreateUser,Salesman,SalesmanAccount,
		ProductName,TypeName,SupplierName,ExecPerson,FinanceAudit,MoveCompanyId,IsHedge,
		IsNull((Select Top 1 EName From Employee Where UserName=CreateUser),CreateUser) As CreateUserEName,
		IsNull(CreateDate,UpTime) as CreateDate,
		IsNull(FinishDate,'1910/01/01') as FinishDate,
		--IsNull((Select top 1 Employee.MobilePhone From Employee Where Employee.UserName=p.ExecPerson),'...') As ExecPersonTel,
		'...' As ExecPersonTel,
		IsNull((Select flag From product Where id=productid),0) As flag
		From Project
		Where OrderId=@OId
		Order By SupplierId,ProductId,id
End